﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Subgurim.Controles;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Net;
using System.Text;
using System.Xml.Linq;

namespace Dragon
{
    public partial class wfUpdateLocation : System.Web.UI.Page
    {
        DataService ds = new DataService();       

        protected void Page_Load(object sender, EventArgs e)
        {
            List<string> roles = new List<string> { "All"};
            if (Session["Username"] == null || roles.Contains(Session["UserType"].ToString()) == false)
            {
                Response.Redirect("~/wfLogin.aspx");
            }

            if (!IsPostBack)
            {
                
            }
        }       

        protected void btnLoadCustomer_Click(object sender, EventArgs e)
        {
            this.lblInformation.Text = "";
            string code = this.txtCode.Text;
            string sql = "select C.Code, C.Name, concat(C.Address, ' ', C.Ward, ' ', D.Name, ' ', P.Name) Address,  C.Latitude, C.Longitude "
                + "from Customer C inner join District D on C.District = D.Code inner join Province P on D.Province = P.Code where C.Code = '" + code + "'";
            DataTable dt = ds.GetData(sql);
            int sodong = dt.Rows.Count;
            if (sodong == 1)
            {
                this.txtName.Text = dt.Rows[0]["Name"].ToString();
                this.txtAddress.Text = dt.Rows[0]["Address"].ToString();
                this.txtLatitude.Text = dt.Rows[0]["Latitude"].ToString();
                this.txtLongitude.Text = dt.Rows[0]["Longitude"].ToString();
            }
        }

        protected void btnLoadLocation_Click(object sender, EventArgs e)
        {
            try
            {
                string address = this.txtAddress.Text;
                string url = "https://maps.google.com/maps/api/geocode/xml?address=" + address + "&sensor=false";
                WebRequest request = WebRequest.Create(url);
                WebResponse response = (HttpWebResponse)request.GetResponse();
                StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);
                DataSet dsResult = new DataSet();
                dsResult.ReadXml(reader);
                if (dsResult.Tables[0].Rows[0][0].ToString() == "OVER_QUERY_LIMIT")
                {
                    this.lblInformation.Text = "Đã hết giới hạn cho phép của Google, thử lại sau 24 tiếng!";
                    return;
                }

                if (dsResult.Tables["result"].Rows.Count > 0)
                {
                    string geometry_id = dsResult.Tables["geometry"].Select("result_id = "
                        + dsResult.Tables["result"].Rows[0]["result_id"].ToString())[0]["geometry_id"].ToString();
                    this.txtLatitude.Text = dsResult.Tables["location"].Select("geometry_id = " + geometry_id)[0]["lat"].ToString();
                    this.txtLongitude.Text = dsResult.Tables["location"].Select("geometry_id = " + geometry_id)[0]["lng"].ToString();
                }
            }
            catch (Exception ex)
            {
                this.lblInformation.Text = ex.Message;
            }
        }       

        protected void btnUpdateAll_Click(object sender, EventArgs e)
        {
            string sql = "select top 2500 C.Code, concat(C.Address, ' ', C.Ward, ' ', D.Name, ' ', P.Name) Address,  C.Latitude, C.Longitude "
            + "from Customer C inner join District D on C.District = D.Code inner join Province P on D.Province = P.Code "
            + "where C.[Status] = 'Approved' and C.LocationType = 0 and (C.Latitude is null or C.Latitude = 0) order by P.OrderLocation";
            DataTable dt = ds.GetData(sql);
            int sodong = dt.Rows.Count;
            string code = "";

            for (int i = 0; i < sodong; i++)
            {               
                try
                {
                    code = dt.Rows[i]["Code"].ToString();
                    string address = dt.Rows[i]["Address"].ToString();
                    string url = "https://maps.google.com/maps/api/geocode/xml?address=" + address + "&sensor=false";
                    WebRequest request = WebRequest.Create(url);
                    WebResponse response = (HttpWebResponse)request.GetResponse();
                    StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);                    
                    DataSet dsResult = new DataSet();
                    dsResult.ReadXml(reader);
                    if (dsResult.Tables[0].Rows[0][0].ToString() == "OVER_QUERY_LIMIT")
                    {
                        System.Threading.Thread.Sleep(1000);                       
                    }

                    if (dsResult.Tables[0].Rows[0][0].ToString() == "ZERO_RESULTS")
                    {
                        sql = "update Customer set LocationType = 2 where Code = '" + code + "'";
                        ds.SetData(sql);
                    }
                    else if (dsResult.Tables["result"].Rows.Count > 0)
                    {
                        string geometry_id = dsResult.Tables["geometry"].Select("result_id = "
                            + dsResult.Tables["result"].Rows[0]["result_id"].ToString())[0]["geometry_id"].ToString();
                        string latitude = dsResult.Tables["location"].Select("geometry_id = " + geometry_id)[0]["lat"].ToString();
                        string longitude = dsResult.Tables["location"].Select("geometry_id = " + geometry_id)[0]["lng"].ToString();
                        sql = "update Customer set Latitude = " + latitude + ", Longitude = " + longitude + " where Code = '" + code + "'";
                        ds.SetData(sql);
                    }                    
                }
                catch (Exception ex)
                {                   
                    this.lblInformation.Text = ex.Message;
                }
            } 
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                string code = this.txtCode.Text;
                double latitude = Convert.ToDouble(this.txtLatitude.Text);
                double longitude = Convert.ToDouble(this.txtLongitude.Text);
                string sql = "update Customer set Latitude = " + latitude + ", Longitude = " + longitude + ", LocationType = 1 where Code = '" + code + "'";
                ds.SetData(sql);
                this.lblInformation.Text = "Cập nhật thành công!";
            }
            catch (Exception ex)
            {
                this.lblInformation.Text = ex.Message;
            }
        }       
    }
}